package SimpleTaskService;

import java.sql.*;
import java.util.ArrayList;

public class DataHelper {

	//private Connection con = null;
	private String sConnectionString = "jdbc:mysql://localhost/dmobileDB";
	private String sUserName = "root";
	private String sPassword = "root";
		
	public UserResponse getUserLogin(String sUserName, String sPassword)
	{
		UserResponse userResponse = new UserResponse();
		UserVO[] user = new UserVO[1];
		user[0] = new UserVO();
						
		ResultSet r = null;
		Boolean bEmptyResultSet = true;
		try
		{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager.getConnection(sConnectionString, this.sUserName, this.sPassword);
			
			if (con != null)
			{
				
				//if(!con.isClosed())
				//	System.out.println("Successfully connected to " + "MySQL server using TCP/IP...");
				
				CallableStatement cs = con.prepareCall("call spcuserlogin(?, ?)");
				
				//System.out.println("before setstring sUseremail");
				cs.setString("sUseremail", sUserName);
				
				//System.out.println("before setstring sUserpassword");
				cs.setString("sUserpassword", sPassword);
				
				r = cs.executeQuery();
				
				//System.out.println("before r");
				
				if (r!=null)
				{				
					while(r.next())
					{
						//System.out.println("in while r");
						
						bEmptyResultSet = false;
						
						int iUserid = r.getInt("userid");
						String sLastLogin = r.getString("lastlogin");
						String sFirstName = r.getString("firstname");
						
						if (iUserid > 0)
						{						
							userResponse.setStatusCode(1);
							userResponse.setStatusMessage("Logon Successful!");
							
							user[0].setUserId(iUserid);
							user[0].setLastLogin(sLastLogin);
							user[0].setFirstName(sFirstName);
							
							userResponse.setUsers(user);
						}
						else
						{
							userResponse.setStatusCode(-1);
							userResponse.setStatusMessage("Invalid User!");
							userResponse.setUsers(user);
						}
						
						break;
					}
					
					if (bEmptyResultSet)
					{
						userResponse.setStatusCode(-1);
						userResponse.setStatusMessage("Invalid User!");
						userResponse.setUsers(user);
					}								
				}
				else
				{
					userResponse.setStatusCode(-2);
					userResponse.setStatusMessage("Invalid User!");
					userResponse.setUsers(user);
				}
			}
			con.close();
			
		}
		catch(Exception E)
		{
			userResponse.setStatusCode(-3);
			userResponse.setStatusMessage(E.getMessage());
			E.printStackTrace();			
		}
		
		return userResponse;
	}
	
	public UserResponse signUpUser(String sUserName, String sPassword, String sFirstName, String sLastName)
	{
		UserResponse userResponse = new UserResponse();
		UserVO[] user = new UserVO[1];
		user[0] = new UserVO();
		
		ResultSet r = null;
		Boolean bEmptyResultSet = true;
		try
		{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager.getConnection(sConnectionString, this.sUserName, this.sPassword);
			
			if (con != null)
			{
				
				//if(!con.isClosed())
				//	System.out.println("Successfully connected to " + "MySQL server using TCP/IP...");
				
				CallableStatement cs = con.prepareCall("call spiuser(?, ?, ?, ?)");
				
				//System.out.println("before setstring sUseremail");
				cs.setString("useremail", sUserName);				
				//System.out.println("before setstring sUserpassword");
				cs.setString("userpassword", sPassword);				
				cs.setString("firstname", sFirstName);				
				cs.setString("lastname", sLastName);
				
				r = cs.executeQuery();
				
				//System.out.println("before r");
				
				if (r!=null)
				{				
					while(r.next())
					{
						//System.out.println("in while r");
						
						bEmptyResultSet = false;
						
						int iUserid = r.getInt("userid");
						String sLastLogin = r.getString("lastlogin");
						String sFName = r.getString("firstname");
						String sLName = r.getString("lastname");
						String sEmail = r.getString("useremail");
						
						if (iUserid > 0)
						{						
							userResponse.setStatusCode(1);
							userResponse.setStatusMessage("Sign Up Successful!");
							
							user[0].setUserId(iUserid);
							user[0].setLastLogin(sLastLogin);
							user[0].setFirstName(sFName);
							user[0].setLastName(sLName);
							user[0].setUserEmail(sEmail);
							
							userResponse.setUsers(user);
							
							
						}
						else
						{
							userResponse.setStatusCode(-1);
							userResponse.setStatusMessage("Could not Sign Up!");
							userResponse.setUsers(user);
						}
						
						break;
					}
					
					if (bEmptyResultSet)
					{
						userResponse.setStatusCode(-2);
						userResponse.setStatusMessage("Useremail already registered!");
						userResponse.setUsers(user);
					}								
				}
				else
				{
					userResponse.setStatusCode(-3);
					userResponse.setStatusMessage("Could not Sign Up!");
					userResponse.setUsers(user);
				}
			}
			con.close();
		}
		catch(Exception E)
		{
			userResponse.setStatusCode(-3);
			userResponse.setStatusMessage(E.getMessage());
			userResponse.setUsers(user);
			E.printStackTrace();			
		}
		
		return userResponse;
	}
	
	public UserTaskListResponse getUserTaskLists(int userid)	 
	{
		UserTaskListResponse response = new UserTaskListResponse();
		UserTaskListVO[] userTaskLists = null;
		ResultSet r = null;
		Boolean bEmptyResultSet = true;
		String sSQL = "call spsusertasklist(?)";
		
		try
		{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager.getConnection(sConnectionString, this.sUserName, this.sPassword);
			
			if (con != null)
			{						
				CallableStatement cs = con.prepareCall(sSQL);				
				cs.setInt("iUserId", userid);				
							
				r = cs.executeQuery();								
				
				if (r!=null)
				{	
					ArrayList<UserTaskListVO> arUtl = new ArrayList<UserTaskListVO>();					
					UserTaskListVO utl = null;
					
					while(r.next())
					{										
						bEmptyResultSet = false;
						
						utl = new UserTaskListVO();
						utl.setUserid(r.getInt("userid"));
						utl.setUsergroupid(r.getInt("usergroupid"));
						utl.setTasklistid(r.getInt("tasklistid"));
						utl.setTasklistname(r.getString("name"));
						utl.setTaskliststatusid(r.getInt("taskliststatusid"));						
						arUtl.add(utl);																		
					}
					
					if (!bEmptyResultSet)					
					{						
						userTaskLists = new UserTaskListVO[arUtl.size()];
						for (int i = 0; i< arUtl.size(); i++)
						{
							userTaskLists[i] = arUtl.get(i);
						}
						response.setStatusCode(1);
						response.setStatusMessage("SUCCESS");
						response.setUsertasklists(userTaskLists);
					}
					else
					{
						response.setStatusCode(2);
						response.setStatusMessage("No Data");
						userTaskLists = new UserTaskListVO[1];
						userTaskLists[0] = new UserTaskListVO();
						response.setUsertasklists(userTaskLists);
					}									
				}			
			}
			con.close();
		}
		catch(Exception E)
		{	
			response.setStatusCode(-1);
			response.setStatusMessage(E.getMessage());
			userTaskLists = new UserTaskListVO[1];
			userTaskLists[0] = new UserTaskListVO();
			response.setUsertasklists(userTaskLists);
			E.printStackTrace();			
		}
		
		return response;
	}
	
	public UserGroupResponse getUserGroups(int userid)	 
	{
		UserGroupResponse response = new UserGroupResponse();
		UserGroupVO[] arrayVO = null;
		ResultSet r = null;
		Boolean bEmptyResultSet = true;
		String sSQL = "call spsusergroups(?)";
		
		try
		{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager.getConnection(sConnectionString, this.sUserName, this.sPassword);
			
			if (con != null)
			{						
				CallableStatement cs = con.prepareCall(sSQL);				
				cs.setInt("iUserId", userid);				
							
				r = cs.executeQuery();								
				
				if (r!=null)
				{	
					ArrayList<UserGroupVO> arl = new ArrayList<UserGroupVO>();					
					UserGroupVO vo = null;
					
					while(r.next())
					{										
						bEmptyResultSet = false;
						
						vo = new UserGroupVO();
						vo.setUsergroupid(r.getInt("usergroupid"));
						vo.setUserid(r.getInt("userid"));
						vo.setGroupid(r.getInt("groupid"));						
						vo.setGroupname(r.getString("groupname"));											
						arl.add(vo);																		
					}
					
					if (!bEmptyResultSet)					
					{						
						arrayVO = new UserGroupVO[arl.size()];
						for (int i = 0; i< arl.size(); i++)
						{
							arrayVO[i] = arl.get(i);
						}
						response.setStatusCode(1);
						response.setStatusMessage("SUCCESS");
						response.setUsergroups(arrayVO);
					}
					else
					{
						response.setStatusCode(2);
						response.setStatusMessage("No Data");
						arrayVO = new UserGroupVO[1];
						arrayVO[0] = new UserGroupVO();
						response.setUsergroups(arrayVO);
					}									
				}			
			}
			con.close();
		}
		catch(Exception E)
		{	
			response.setStatusCode(-1);
			response.setStatusMessage(E.getMessage());
			arrayVO = new UserGroupVO[1];
			arrayVO[0] = new UserGroupVO();
			response.setUsergroups(arrayVO);
			E.printStackTrace();			
		}
		
		return response;
	}
	
	public UserTaskResponse getTasks(int tasklistid)	 
	{
		UserTaskResponse response = new UserTaskResponse();
		UserTaskVO[] arrayVO = null;
		ResultSet r = null;
		Boolean bEmptyResultSet = true;
		String sSQL = "call spstasks(?)";
		
		try
		{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager.getConnection(sConnectionString, this.sUserName, this.sPassword);
			
			if (con != null)
			{						
				CallableStatement cs = con.prepareCall(sSQL);				
				cs.setInt("iTasklistId", tasklistid);				
							
				r = cs.executeQuery();								
				
				if (r!=null)
				{	
					ArrayList<UserTaskVO> arl = new ArrayList<UserTaskVO>();					
					UserTaskVO vo = null;
					
					while(r.next())
					{										
						bEmptyResultSet = false;
						
						vo = new UserTaskVO();
						vo.setTaskid(r.getInt("taskid"));												
						vo.setName(r.getString("name"));
						vo.setTaskstatusid(r.getInt("taskstatusid"));
						vo.setStatus(r.getString("status"));
						vo.setTasklistid(r.getInt("tasklistid"));
						arl.add(vo);																		
					}
					
					if (!bEmptyResultSet)					
					{						
						arrayVO = new UserTaskVO[arl.size()];
						for (int i = 0; i< arl.size(); i++)
						{
							arrayVO[i] = arl.get(i);
						}
						response.setStatusCode(1);
						response.setStatusMessage("SUCCESS");
						response.setUsertasks(arrayVO);
					}
					else
					{
						response.setStatusCode(2);
						response.setStatusMessage("No Data");
						arrayVO = new UserTaskVO[1];
						arrayVO[0] = new UserTaskVO();
						response.setUsertasks(arrayVO);
					}									
				}			
			}
			con.close();
		}
		catch(Exception E)
		{	
			response.setStatusCode(-1);
			response.setStatusMessage(E.getMessage());
			arrayVO = new UserTaskVO[1];
			arrayVO[0] = new UserTaskVO();
			response.setUsertasks(arrayVO);
			E.printStackTrace();			
		}
		
		return response;
	}

	public UserGroupResponse createGroup(int userid, String groupname)
	{
		UserGroupResponse response = new UserGroupResponse();
		UserGroupVO[] arrayVO = null;
		ResultSet r = null;
		Boolean bEmptyResultSet = true;
		String sSQL = "call spigroupforuser(?, ?)";
		
		try
		{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager.getConnection(sConnectionString, this.sUserName, this.sPassword);
			
			if (con != null)
			{						
				CallableStatement cs = con.prepareCall(sSQL);				
				cs.setInt("iuserid", userid);	
				cs.setString("sgroupname", groupname);	
								
				r = cs.executeQuery();								
				
				if (r!=null)
				{	
					ArrayList<UserGroupVO> arl = new ArrayList<UserGroupVO>();					
					UserGroupVO vo = null;
					
					while(r.next())
					{										
						bEmptyResultSet = false;
						
						vo = new UserGroupVO();
						vo.setUsergroupid(r.getInt("usergroupid"));
						vo.setUserid(r.getInt("userid"));
						vo.setGroupid(r.getInt("groupid"));						
						vo.setGroupname(r.getString("groupname"));											
						arl.add(vo);																		
					}
					
					if (!bEmptyResultSet)					
					{						
						arrayVO = new UserGroupVO[arl.size()];
						for (int i = 0; i< arl.size(); i++)
						{
							arrayVO[i] = arl.get(i);
						}
						response.setStatusCode(1);
						response.setStatusMessage("SUCCESS");
						response.setUsergroups(arrayVO);
					}
					else
					{
						response.setStatusCode(2);
						response.setStatusMessage("No Data");
						arrayVO = new UserGroupVO[1];
						arrayVO[0] = new UserGroupVO();
						response.setUsergroups(arrayVO);
					}									
				}			
			}
			con.close();
		}
		catch(Exception E)
		{	
			response.setStatusCode(-1);
			response.setStatusMessage(E.getMessage());
			arrayVO = new UserGroupVO[1];
			arrayVO[0] = new UserGroupVO();
			response.setUsergroups(arrayVO);
			E.printStackTrace();			
		}
		
		return response;
	}

	public UserTaskListResponse createTaskList(int usergroupid, String tasklistname)
	{
		UserTaskListResponse response = new UserTaskListResponse();
		UserTaskListVO[] userTaskLists = null;
		ResultSet r = null;
		Boolean bEmptyResultSet = true;
		String sSQL = "call spitasklist(?, ?, ?)";
		
		try
		{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager.getConnection(sConnectionString, this.sUserName, this.sPassword);
			
			if (con != null)
			{						
				CallableStatement cs = con.prepareCall(sSQL);				
				cs.setString("sname", tasklistname);				
				cs.setInt("iusergroupid", usergroupid);
				cs.setInt("itaskliststatusid", 3);
				
				r = cs.executeQuery();								
				
				if (r!=null)
				{	
					ArrayList<UserTaskListVO> arUtl = new ArrayList<UserTaskListVO>();					
					UserTaskListVO utl = null;
					
					while(r.next())
					{										
						bEmptyResultSet = false;
						
						utl = new UserTaskListVO();
						//utl.setUserid(r.getInt("userid"));
						utl.setUsergroupid(r.getInt("usergroupid"));
						utl.setTasklistid(r.getInt("tasklistid"));
						utl.setTasklistname(r.getString("name"));
						utl.setTaskliststatusid(r.getInt("taskliststatusid"));						
						arUtl.add(utl);																		
					}
					
					if (!bEmptyResultSet)					
					{						
						userTaskLists = new UserTaskListVO[arUtl.size()];
						for (int i = 0; i< arUtl.size(); i++)
						{
							userTaskLists[i] = arUtl.get(i);
						}
						response.setStatusCode(1);
						response.setStatusMessage("SUCCESS");
						response.setUsertasklists(userTaskLists);
					}
					else
					{
						response.setStatusCode(2);
						response.setStatusMessage("No Data");
						userTaskLists = new UserTaskListVO[1];
						userTaskLists[0] = new UserTaskListVO();
						response.setUsertasklists(userTaskLists);
					}									
				}			
			}
			con.close();
		}
		catch(Exception E)
		{	
			response.setStatusCode(-1);
			response.setStatusMessage(E.getMessage());
			userTaskLists = new UserTaskListVO[1];
			userTaskLists[0] = new UserTaskListVO();
			response.setUsertasklists(userTaskLists);
			E.printStackTrace();			
		}
		
		return response;
	}

	public UserTaskListResponse updateTaskListStatus(int tasklistid, int taskliststatusid)
	{
		UserTaskListResponse response = new UserTaskListResponse();
		UserTaskListVO[] userTaskLists = null;
		ResultSet r = null;
		Boolean bEmptyResultSet = true;
		String sSQL = "call sputaskliststatus(?, ?)";
		
		try
		{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager.getConnection(sConnectionString, this.sUserName, this.sPassword);
			
			if (con != null)
			{						
				CallableStatement cs = con.prepareCall(sSQL);											
				cs.setInt("iTaskListId", tasklistid);
				cs.setInt("iTaskListStatusID", taskliststatusid);
				
				r = cs.executeQuery();								
				
				if (r!=null)
				{	
					ArrayList<UserTaskListVO> arUtl = new ArrayList<UserTaskListVO>();					
					UserTaskListVO utl = null;
					
					while(r.next())
					{										
						bEmptyResultSet = false;
						
						utl = new UserTaskListVO();
						//utl.setUserid(r.getInt("userid"));
						utl.setUsergroupid(r.getInt("usergroupid"));
						utl.setTasklistid(r.getInt("tasklistid"));
						utl.setTasklistname(r.getString("name"));
						utl.setTaskliststatusid(r.getInt("taskliststatusid"));						
						arUtl.add(utl);																		
					}
					
					if (!bEmptyResultSet)					
					{						
						userTaskLists = new UserTaskListVO[arUtl.size()];
						for (int i = 0; i< arUtl.size(); i++)
						{
							userTaskLists[i] = arUtl.get(i);
						}
						response.setStatusCode(1);
						response.setStatusMessage("SUCCESS");
						response.setUsertasklists(userTaskLists);
					}
					else
					{
						response.setStatusCode(2);
						response.setStatusMessage("No Data");
						userTaskLists = new UserTaskListVO[1];
						userTaskLists[0] = new UserTaskListVO();
						response.setUsertasklists(userTaskLists);
					}									
				}			
			}
			con.close();
		}
		catch(Exception E)
		{	
			response.setStatusCode(-1);
			response.setStatusMessage(E.getMessage());
			userTaskLists = new UserTaskListVO[1];
			userTaskLists[0] = new UserTaskListVO();
			response.setUsertasklists(userTaskLists);
			E.printStackTrace();			
		}
		
		return response;
	}
	
	public UserTaskResponse creatTask(int tasklistid, String taskname)
	{
		UserTaskResponse response = new UserTaskResponse();
		UserTaskVO[] arrayVO = null;
		ResultSet r = null;
		Boolean bEmptyResultSet = true;
		String sSQL = "call spitask(?, ?)";
		
		try
		{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager.getConnection(sConnectionString, this.sUserName, this.sPassword);
			
			if (con != null)
			{						
				CallableStatement cs = con.prepareCall(sSQL);	
				cs.setString("sname", taskname);
				cs.setInt("iTasklistId", tasklistid);
				cs.setInt("itaskstatusid", 1);
							
				r = cs.executeQuery();								
				
				if (r!=null)
				{	
					ArrayList<UserTaskVO> arl = new ArrayList<UserTaskVO>();					
					UserTaskVO vo = null;
					
					while(r.next())
					{										
						bEmptyResultSet = false;
						
						vo = new UserTaskVO();
						vo.setTaskid(r.getInt("taskid"));												
						vo.setName(r.getString("name"));
						vo.setTaskstatusid(r.getInt("taskstatusid"));
						vo.setStatus(r.getString("status"));
						vo.setTasklistid(r.getInt("tasklistid"));
						arl.add(vo);																		
					}
					
					if (!bEmptyResultSet)					
					{						
						arrayVO = new UserTaskVO[arl.size()];
						for (int i = 0; i< arl.size(); i++)
						{
							arrayVO[i] = arl.get(i);
						}
						response.setStatusCode(1);
						response.setStatusMessage("SUCCESS");
						response.setUsertasks(arrayVO);
					}
					else
					{
						response.setStatusCode(2);
						response.setStatusMessage("No Data");
						arrayVO = new UserTaskVO[1];
						arrayVO[0] = new UserTaskVO();
						response.setUsertasks(arrayVO);
					}									
				}			
			}
			con.close();
		}
		catch(Exception E)
		{	
			response.setStatusCode(-1);
			response.setStatusMessage(E.getMessage());
			arrayVO = new UserTaskVO[1];
			arrayVO[0] = new UserTaskVO();
			response.setUsertasks(arrayVO);
			E.printStackTrace();			
		}
		
		return response;
	}
	
	public UserTaskResponse updateTaskStatus(int taskid, int taskstatusid)
	{
		UserTaskResponse response = new UserTaskResponse();
		UserTaskVO[] arrayVO = null;
		ResultSet r = null;
		Boolean bEmptyResultSet = true;
		String sSQL = "call sputaskstatus(?, ?)";
		
		try
		{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager.getConnection(sConnectionString, this.sUserName, this.sPassword);
			
			if (con != null)
			{						
				CallableStatement cs = con.prepareCall(sSQL);				
				cs.setInt("iTaskId", taskid);
				cs.setInt("iTaskStatusID", taskstatusid);
							
				r = cs.executeQuery();								
				
				if (r!=null)
				{	
					ArrayList<UserTaskVO> arl = new ArrayList<UserTaskVO>();					
					UserTaskVO vo = null;
					
					while(r.next())
					{										
						bEmptyResultSet = false;
						
						vo = new UserTaskVO();
						vo.setTaskid(r.getInt("taskid"));												
						vo.setName(r.getString("name"));
						vo.setTaskstatusid(r.getInt("taskstatusid"));
						vo.setStatus(r.getString("status"));
						vo.setTasklistid(r.getInt("tasklistid"));
						arl.add(vo);																		
					}
					
					if (!bEmptyResultSet)					
					{						
						arrayVO = new UserTaskVO[arl.size()];
						for (int i = 0; i< arl.size(); i++)
						{
							arrayVO[i] = arl.get(i);
						}
						response.setStatusCode(1);
						response.setStatusMessage("SUCCESS");
						response.setUsertasks(arrayVO);
					}
					else
					{
						response.setStatusCode(2);
						response.setStatusMessage("No Data");
						arrayVO = new UserTaskVO[1];
						arrayVO[0] = new UserTaskVO();
						response.setUsertasks(arrayVO);
					}									
				}			
			}
			con.close();
		}
		catch(Exception E)
		{	
			response.setStatusCode(-1);
			response.setStatusMessage(E.getMessage());
			arrayVO = new UserTaskVO[1];
			arrayVO[0] = new UserTaskVO();
			response.setUsertasks(arrayVO);
			E.printStackTrace();			
		}
		
		return response;
	}	
}
